package ecom.appcode.dao.daointerface;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import ecom.appcode.common.bean.OrderBean;
import ecom.appcode.common.bean.OrderDetailBean;
import ecom.appcode.common.daobean.OrderDetailDaoBean;
import ecom.appcode.common.daobean.UsersDaoBean;
import ecom.appcode.common.utility.DBUltils;

public class OrderDetailDaoImpl implements OrderDetailDao {

    /** Insert Order Details */
    public boolean insertOrderDetails(int shoesID, int quantity, float unitPrice, float total, int orderID) {

        Connection con = null;
        PreparedStatement stm = null;

        try {
            con = DBUltils.makeConnection();
            String sql = "INSERT INTO tblOrderDetail VALUES(?,?,?,?,?)";
            stm = con.prepareStatement(sql);

            stm.setInt(1, shoesID);
            stm.setInt(2, quantity);
            stm.setFloat(3, unitPrice);
            stm.setFloat(4, total);
            stm.setInt(5, orderID);

            int row = stm.executeUpdate();
            if (row > 0) {
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (stm != null) {
                    stm.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (con != null) {
                    con.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return false;
    }

    /** SELECT BEST PRODUCT */
    public List<OrderDetailBean> selectBestProduct() {
        Connection con = null;
        PreparedStatement stm = null;
        ResultSet rs = null;
        try {
            con = DBUltils.makeConnection();
            String sql = "SELECT TOP(4) WITH TIES shoesID, SUM(quantity) as Tongsoluong FROM tblOrderDetail GROUP BY shoesID ORDER BY Tongsoluong desc ";
            stm = con.prepareStatement(sql);
            rs = stm.executeQuery();
            List<OrderDetailBean> list = new ArrayList<OrderDetailBean>();
            while (rs.next()) {
                /** Email */
                int shoesID = rs.getInt("shoesID");
                OrderDetailBean orderBean = new OrderDetailBean(shoesID);
                list.add(orderBean);
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (stm != null) {
                    stm.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (con != null) {
                    con.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    /** SELECT ALL ORDER DETAIL */
    public List<OrderDetailDaoBean> viewAllOrderDetail() {
        Connection con = null;
        PreparedStatement stm = null;
        ResultSet rs = null;
        try {
            con = DBUltils.makeConnection();
            String sql = "SELECT * FROM tblOrderDetail";
            stm = con.prepareStatement(sql);
            rs = stm.executeQuery();
            List<OrderDetailDaoBean> list = new ArrayList<OrderDetailDaoBean>();
            while (rs.next()) {

                int ID = rs.getInt("ID");
                int shoesID = rs.getInt("shoesID");
                int quantity = rs.getInt("quantity");
                float unitPrice = rs.getFloat("unitPrice");
                float total = rs.getInt("total");
                int orderID = rs.getInt("orderID");

                OrderDetailDaoBean order = new OrderDetailDaoBean(ID, shoesID, quantity, unitPrice, total, orderID);

                list.add(order);
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (stm != null) {
                    stm.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (con != null) {
                    con.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }
        return null;
    }

    /** SELECT ALL ORDER DETAIL AND ORDER */
    public List<OrderBean> loadOrderAndDetail() {
        Connection con = null;
        PreparedStatement stm = null;
        ResultSet rs = null;
        try {
            con = DBUltils.makeConnection();
            String sql = "SELECT t002.ID,t001.orderDate,t002.shoesID,t002.quantity,t002.unitPrice,t001.email,shipDate ,t002.orderID,t003.name,t001.transacStatus FROM tblOrder t001,tblOrderDetail t002,tblShoes t003 WHERE t001.orderID = t002.orderID AND t003.shoesID = t002.shoesID ORDER BY orderID ";
            stm = con.prepareStatement(sql);
            rs = stm.executeQuery();
            List<OrderBean> list = new ArrayList<OrderBean>();
            while (rs.next()) {
                int id = rs.getInt("ID");
                String orderDate = rs.getString("orderDate");
                String email = rs.getString("email");
                boolean transacStatus = rs.getBoolean("transacStatus");
                int shoesID = rs.getInt("shoesID");
                int quantity = rs.getInt("quantity");
                float unitPrice = rs.getInt("unitPrice");
                String shipDate = rs.getString("shipDate");
                int orderID = rs.getInt("orderID");
                String shoesName = rs.getString("name");

                OrderBean order = new OrderBean(id, orderDate, email, transacStatus, shoesID, quantity, unitPrice, shipDate, orderID, shoesName);
                list.add(order);
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (stm != null) {
                    stm.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (con != null) {
                    con.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }
        return null;
    }
    
    /** Delete orderDaoBean */
    public boolean deleteOrderDetail(int ID){
        Connection con = null;
        PreparedStatement stm = null;

        try {
            con = DBUltils.makeConnection();
            String sql = "DELETE FROM tblOrderDetail WHERE ID = ?";
            stm = con.prepareStatement(sql);
            stm.setInt(1, ID);

            int row = stm.executeUpdate();
            if (row > 0) {
                return true;
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (stm != null) {
                    stm.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (con != null) {
                    con.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return false;
    }
    
    /** Update Category */
    public boolean updateOrderDetail(int categoryID, String categoryName, String description) {
        Connection con = null;
        PreparedStatement stm = null;

        try {
            con = DBUltils.makeConnection();
            String sql = "UPDATE tblCategory SET categoryName = ?, description = ? WHERE categoryID = ?";
            stm = con.prepareStatement(sql);

            stm.setString(1, categoryName);
            stm.setString(2, description);
            stm.setInt(3, categoryID);

            int row = stm.executeUpdate();
            if (row > 0) {
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (stm != null) {
                    stm.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (con != null) {
                    con.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return false;
    }
}
